package com.fiap.gestaoPassagens.cliente;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import com.fiap.gestaoPassagens.dao.ConnectionFactory;
import com.fiap.gestaoPassagens.endereco.EnderecoDao;
import com.fiap.gestaoPassagens.voo.VooBean;

public class ClienteDao {
	public ClienteBean createClienteBean(ClienteBean clienteBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs;
		int i = 0;

		try {
			sql.append(" INSERT INTO db_fiap.cliente ");
			sql.append(" (NOME, ");
			sql.append(" CPF, ");
			sql.append(" EMAIL, ");
			sql.append(" CELULAR, ");
			sql.append(" FONE, ");
			sql.append(" USUARIO, ");
			sql.append(" SENHA) ");
			sql.append(" VALUES(?,?,?,?,?,?,?) ");


			pstmt = con.prepareStatement(sql.toString(),
					Statement.RETURN_GENERATED_KEYS);

			pstmt.setString(++i, clienteBean.getNome());
			pstmt.setLong(++i, clienteBean.getCpf());
			pstmt.setString(++i, clienteBean.getEmail());
			pstmt.setString(++i, clienteBean.getCelular());
			pstmt.setString(++i, clienteBean.getFone());
			pstmt.setString(++i, clienteBean.getUsuario());
			pstmt.setString(++i, clienteBean.getSenha());
			
			pstmt.executeUpdate();

			// Retorna o ID gerado.
			rs = pstmt.getGeneratedKeys();
			if (rs.next()) {
				clienteBean.setId(rs.getLong(1));
				EnderecoDao enderecoDao = new EnderecoDao();
				clienteBean.getEndereco().setIdPessoa(clienteBean.getId());
				enderecoDao.create(clienteBean.getEndereco());
			}

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}

		return clienteBean;
	}

	public ClienteBean updateClienteBean(ClienteBean clienteBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i = 0;
		try {
			sql.append(" UPDATE db_fiap.cliente ");
			sql.append(" SET ");
			sql.append(" NOME    = ?, ");
			sql.append(" CPF     = ?, ");
			sql.append(" EMAIL   = ?, ");
			sql.append(" CELULAR = ?, ");
			sql.append(" FONE    = ? ");
			sql.append(" WHERE ID= ? ");

			pstmt = con.prepareStatement(sql.toString());

			pstmt.setString(++i, clienteBean.getFone());
			pstmt.setLong(++i, clienteBean.getCpf());
			pstmt.setString(++i, clienteBean.getEmail());
			pstmt.setString(++i, clienteBean.getCelular());
			pstmt.setString(++i, clienteBean.getFone());
			pstmt.setLong(++i, clienteBean.getId());
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			pstmt.close();
			con.close();
		}

		return clienteBean;
	}

	public void deleteClienteBean(ClienteBean clienteBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i = 0;
		try {
			sql.append(" DELETE FROM db_fiap.cliente ");
			sql.append(" WHERE ID = ? ");

			pstmt = con.prepareStatement(sql.toString());

			pstmt.setLong(++i, clienteBean.getId());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	public List<ClienteBean> listCliente(ClienteBean clienteFiltros	) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		List<ClienteBean> listaClientes = new ArrayList<ClienteBean>();
		ClienteBean clienteBean = null;
		int i=0;

		try {
			sql.append(" SELECT ");
			sql.append(" ID, ");
			sql.append(" NOME, ");
			sql.append(" CPF, ");
			sql.append(" EMAIL, ");
			sql.append(" CELULAR, ");
			sql.append(" FONE, ");
			sql.append(" USUARIO, ");
			sql.append(" SENHA, ");
			sql.append(" ADMIN ");
			sql.append(" FROM db_fiap.cliente ");
			sql.append(" WHERE 1 = 1 ");
			
			if(clienteFiltros.getId()>0){
				sql.append("   AND ID  = ? ");
			}			
			
			if(StringUtils.trimToNull(clienteFiltros.getNome())!=null){
				sql.append("   AND upper(NOME) like upper('%?%') ");
			}
			
			if(clienteFiltros.getCpf()>0){
				sql.append("   AND CPF  = ? ");
			}
			
			if(StringUtils.trimToNull(clienteFiltros.getUsuario())!=null){
				sql.append("   AND USUARIO = ? ");
			}
			
			if(StringUtils.trimToNull(clienteFiltros.getSenha())!=null){
				sql.append("   AND SENHA = ? ");
			}			

			pstmt = con.prepareStatement(sql.toString());
			
			if(clienteFiltros.getId()>0){
				pstmt.setLong(++i, clienteFiltros.getId());
			}
			
			if(StringUtils.trimToNull(clienteFiltros.getNome())!=null){
				pstmt.setString(++i, clienteFiltros.getNome());
			}
			
			if(clienteFiltros.getCpf()>0){
				pstmt.setLong(++i, clienteFiltros.getCpf());
			}
			
			if(StringUtils.trimToNull(clienteFiltros.getUsuario())!=null){
				pstmt.setString(++i, clienteFiltros.getUsuario());
			}
			
			if(StringUtils.trimToNull(clienteFiltros.getSenha())!=null){
				pstmt.setString(++i, clienteFiltros.getSenha());
			}			
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				clienteBean = new ClienteBean();
				clienteBean.setId(rs.getLong("ID"));
				clienteBean.setNome(rs.getString("NOME"));
				clienteBean.setCpf(rs.getLong("CPF"));
				clienteBean.setEmail(rs.getString("EMAIL"));
				clienteBean.setFone(rs.getString("FONE"));
				clienteBean.setCelular(rs.getString("CELULAR"));
				clienteBean.setUsuario(rs.getString("USUARIO"));
				clienteBean.setSenha(rs.getString("SENHA"));
				clienteBean.setUsuarioAdmin(rs.getBoolean("ADMIN"));
				listaClientes.add(clienteBean);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return listaClientes;
	}


	public VooBean mountBean(ResultSet rs) throws SQLException {
		VooBean v = new VooBean();
		v.setId(rs.getLong("ID"));
		v.setCodigoVoo(rs.getString("CODIGO_VOO"));
		v.setDataPrevistaDecolagem(new Date(rs.getTimestamp(
				"DT_PREVISTA_DECOLAGEM").getTime()));// (new
														// java.util.Date(rs.getTime("DT_PREVISTA_DECOLAGEM").getTime()));
		v.setDataPrevistaPouso(new Date(rs.getTimestamp("DT_PREVISTA_POUSO")
				.getTime()));
		v.setDestinoVo(rs.getString("DESTINO_VOO"));
		v.setOrigemVoo(rs.getString("ORIGEM_VOO"));

		return v;
	}
}
